const Router = require('koa-router');
const {db, Op,QueryTypes } = require("../../models/db");
const tool = require("../../tools/tool");
const router = new Router();
// 手机销售汇总
router.post('/app/getSaleGp', async ctx => {
    try {
        let user = ctx.user;
        let tenantId = user.tenantId;
        let uid = user.id;
        let body = ctx.request.body;
        let currentPage= body.currentPage;
        let gpType= body.gpType;
        let beginDate= body.beginDate?body.beginDate:'';
        let endDate= body.endDate?body.endDate:'';
        let keyword= body.keyWord?body.keyWord:'';
        let pageSize= body.pageSize?body.pageSize:30;
        let offset = (currentPage - 1) * pageSize;
        let salesName= body.salesName?body.salesName:'';
        let buName= body.buName?body.buName:'';
        let invName= body.invName?body.invName:'';
        let searchType= body.searchType?body.searchType:'quick';
        let sql = `select a.buName,a.billNo,a.buId,a.totalAmountReal,b.amountReal,b.invName,b.invId,b.spec,b.qtyReal*b.calNum qty,b.unit1,b.unit2 
                         from v_main a inner join v_main_info b on a.tenantId=b.tenantId and a.id=b.iid where 
                         a.tenantId=${tenantId} and a.transType=150601 and a.billState='accounted'
                         `
        if(searchType === 'quick') {
            if(keyword) {
                sql += ` and (buName like '%${keyword}%' or b.invName like '%${keyword}%') `
            }
            if(beginDate) {
                sql += ` and billDate>='${beginDate}' `
            }
            if(endDate) {
                sql += ` and billDate<='${endDate}' `
            }
            sql += ` and salesName='${user.name}' `
        }
        else {
            if(beginDate) {
                sql += ` and billDate>='${beginDate}' `
            }
            if(endDate) {
                sql += ` and billDate<='${endDate}' `
            }
            if(buName) {
                sql += ` and buName like '%${buName}%' `
            }
            if(invName) {
                sql += ` and b.invName like '%${invName}%' `
            }
            if(salesName) {
                sql += ` and salesName='${salesName}' `
            }
        }
        let xsesql = `select sum(totalAmountReal) amount from (select distinct billNo, totalAmountReal from (${sql}) a) a   `
        let ordersql = `select count(*) total from (select billNo from (${sql}) a group by billNo) a `
        let cusql = `select count(*) total from (select buId from (${sql}) a group by buId) a `

        if(gpType === '客户汇总') {
            sql = `select buName name,sum(totalAmountReal) amount from (select distinct billNo,buName,totalAmountReal from (${sql}) a) a group by buName `
            sql = ` select * from (${sql}) a order by name  desc `
        }
        if(gpType === '商品汇总') {
            sql = `select name,amount,dbo.setAssQty(qty,spec,unit1,unit2) qty from (select invName name,spec,unit1,unit2,sum(amountReal) amount,sum(qty) qty from (${sql}) a group by invName,spec,unit1,unit2) a  `
            sql = ` select * from (${sql}) a order by name,qty desc `
        }
        sql += ` offset ${offset} rows fetch next ${pageSize} rows only; `
        let invoice = await db.query(sql, {type: QueryTypes.SELECT})
        invoice.map(row => {
            row.amount = tool.xe.toFixed(row.amount, 2)
        })
        let xs = await db.query(xsesql, {type: QueryTypes.SELECT, plain: true})
        let orders = await db.query(ordersql, {type: QueryTypes.SELECT, plain: true})
        let cus = await db.query(cusql, {type: QueryTypes.SELECT, plain: true})
        return ctx.suc('', {
            total: 0,
            list: invoice,
            amount: tool.xe.toFixed(xs.amount,2),
            orders: orders.total,
            customers: cus.total
        })
    } catch (e) {
        return ctx.err(e)
    }

})


module.exports = router;
